#!/use/bin/python
# coding=utf-8
# 早间开机 数据分析-追剧提醒-服务推送成功率
import datetime
import pymysql

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# 记录 数据分析-早间开机-服务推送成功率 analysis_zjtx_push_rate
def insertPushRate(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zjtx_push_rate 表是否已经存在当天 数据
        selectCountSql = """
                    select count(1) num from analysis_zjtx_push_rate t where 1=1 and t.ref_date =  '%s'
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日追剧提醒 展示次数
        selectNumSql = """
                    select count(1) from log_zjtx_page_show t where 1=1 and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s'
                """ % date;
        cur.execute(selectNumSql)
        resultNum = cur.fetchone()
        # 获取当日追剧提醒 展示设备量(去重)
        selectActiveIdNumSql = """
                            select count(DISTINCT(t.active_id))  from log_zjtx_page_show t where 1=1 and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s'
                        """ % date;
        cur.execute(selectActiveIdNumSql)
        resultActiveIdNum = cur.fetchone()
        # 获取所有追剧提醒 展示次数
        selectAllNumSql = """
                           select count(1) from log_zjtx_page_show t 
                       """;
        cur.execute(selectAllNumSql)
        resultAllNum = cur.fetchone()
        # 计算 当天点开剧集的数量
        selectOkNumSql = """
                            select count(1) from log_zjtx_page_aciton t where t.choose = 'ok' and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s'
                        """ % date;
        cur.execute(selectOkNumSql)
        resultOkNum = cur.fetchone()
        # 计算 当天息屏及自动关机的数量
        selectExitNumSql = """
                            select count(1) from log_zjtx_page_aciton t where (t.choose = 'exit' or t.choose = 'poweroff ') and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s'
                        """ % date;
        cur.execute(selectExitNumSql)
        resultExitNum = cur.fetchone()
        # 计算剧集提醒推送成功率
        okRate = 0
        homeRate = 0
        exitRate = 0
        okNum = resultOkNum[0]
        exitNum = resultExitNum[0]
        newPushNum = resultNum[0]
        newActiveIdNum = resultActiveIdNum[0]
        #除息屏及用户关机外。就是进入主页的数量
        homeNum = newPushNum - okNum - exitNum
        if homeNum<0:
            homeNum=0
        cumulatePushNum = resultAllNum[0]
        if newPushNum!=0:
            if okNum > newPushNum:
                okRate = 100
            else:
                okRate = round(okNum/newPushNum,4)*100
            if homeNum > newPushNum:
                homeRate = 100
            else:
                homeRate = round(homeNum/newPushNum,4)*100
            if exitNum > newPushNum:
                exitRate = 100
            else:
                exitRate = round(exitNum/newPushNum,4)*100

        if countNum == 0:
            logDetail ="%s日新增-追剧提醒推送数量为%s,设备量为%s,推送累计次数为%s,进入主页的数量为%s,比例为%s,播放剧集的数量为%s,比例为%s,自动息屏或用户主动关机的数量为%s,比例为%s" % (
                date,newPushNum,newActiveIdNum,cumulatePushNum,homeNum,homeRate,okNum,okRate,exitNum,exitRate)
            print(logDetail)
            # 插入追剧提醒推送成功率
            insertSql = """insert into analysis_zjtx_push_rate(show_num,show_device_num,cumulate_num,home_num,home_rate,play_num,play_rate,shutdown_num,shutdown_rate,ref_date) 
                value(%s,%s,%s,%s,%s,%s,%s,%s,%s,'%s')""" % (newPushNum,newActiveIdNum,cumulatePushNum,homeNum,homeRate,okNum,okRate,exitNum,exitRate,date)
            cur.execute(insertSql)
        else:
            logDetail = "%s日更新-追剧提醒推送数量为%s,设备量为%s,推送累计次数为%s,进入主页的数量为%s,比例为%s,播放剧集的数量为%s,比例为%s,自动息屏或用户主动关机的数量为%s,比例为%s" % (
                date, newPushNum, newActiveIdNum,cumulatePushNum, homeNum, homeRate, okNum, okRate, exitNum, exitRate)
            print(logDetail)
            # 更新追剧提醒推送成功率
            updateSql = """
                update analysis_zjtx_push_rate set show_num = %s,show_device_num = %s ,cumulate_num = %s,home_num = %s ,home_rate = %s,play_num = %s,play_rate = %s,shutdown_num = %s ,shutdown_rate = %s ,update_time = now()  where
                    ref_date = '%s' 
            """ % (newPushNum,newActiveIdNum,cumulatePushNum,homeNum,homeRate, okNum, okRate,exitNum,exitRate,date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 记录 数据分析-早间开机-服务推送成功率 analysis_zjtx_push_rate --月度
def insertPushRateMonth(toDay,toMonthDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zjtx_push_rate 表是否已经存在当天 数据
        selectCountSql = """
                    select count(1) num from analysis_zjtx_push_rate t where 1=1 and t.ref_date =  '%s'
                """ % toMonthDay
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当月追剧提醒 展示次数
        selectNumSql = """
                    select sum(show_num) from analysis_zjtx_push_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s'
                """ % (toMonthDay,toMonthDay);
        cur.execute(selectNumSql)
        resultNum = cur.fetchone()
        # 获取当月追剧提醒 展示设备数(去重)
        selectActiveIdNumSql = """
                            select sum(show_device_num) from analysis_zjtx_push_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s'
                        """ % (toMonthDay, toMonthDay);
        cur.execute(selectActiveIdNumSql)
        resultActiveIdNum = cur.fetchone()
        # 获取所有追剧提醒 展示次数
        selectAllNumSql = """
                    select cumulate_num from analysis_zjtx_push_rate t where ref_date = '%s' 
                """% toDay;
        cur.execute(selectAllNumSql)
        resultAllNum = cur.fetchone()
        #计算 当月进入主页的数量
        selectHomeNumSql = """
                    select sum(home_num) from analysis_zjtx_push_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s'
                """ % (toMonthDay,toMonthDay);
        cur.execute(selectHomeNumSql)
        resultHomeNum = cur.fetchone()
        # 计算 当月点开剧集的数量
        selectOkNumSql = """
                            select sum(play_num) from analysis_zjtx_push_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s'
                        """ % (toMonthDay, toMonthDay);
        cur.execute(selectOkNumSql)
        resultOkNum = cur.fetchone()
        # 计算 当月息屏的数量
        selectExitNumSql = """
                    select sum(shutdown_num) from analysis_zjtx_push_rate t where 1=1 and SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s'
                """ % (toMonthDay,toMonthDay);
        cur.execute(selectExitNumSql)
        resultExitNum = cur.fetchone()
        # 计算剧集提醒推送成功率
        okRate = 0
        homeRate = 0
        exitRate = 0
        okNum = resultOkNum[0]
        homeNum = resultHomeNum[0]
        exitNum = resultExitNum[0]
        newPushNum = resultNum[0]
        cumulatePushNum = resultAllNum[0]
        newActiveIdNum = resultActiveIdNum[0]
        if newPushNum!=0:
            if okNum > newPushNum:
                okRate = 100
            else:
                okRate = round(okNum / newPushNum, 4) * 100
            if homeNum > newPushNum:
                homeRate = 100
            else:
                homeRate = round(homeNum/newPushNum,4)*100
            if exitNum > newPushNum:
                exitRate = 100
            else:
                exitRate = round(exitNum/newPushNum,4)*100

        if countNum == 0:
            logDetail = "%s日新增-追剧提醒推送数量为%s,设备量为%s,推送累计次数为%s,进入主页的数量为%s,比例为%s,播放剧集的数量为%s,比例为%s,自动息屏或用户主动关机的数量为%s,比例为%s" % (
                toMonthDay, newPushNum, newActiveIdNum, cumulatePushNum, homeNum, homeRate, okNum, okRate, exitNum, exitRate)
            print(logDetail)
            # 插入追剧提醒推送成功率
            insertSql = """insert into analysis_zjtx_push_rate(show_num,show_device_num,cumulate_num,home_num,home_rate,play_num,play_rate,shutdown_num,shutdown_rate,ref_date) 
                            value(%s,%s,%s,%s,%s,%s,%s,%s,%s,'%s')""" % (
            newPushNum,  newActiveIdNum, cumulatePushNum,homeNum, homeRate, okNum, okRate, exitNum, exitRate, toMonthDay)
            cur.execute(insertSql)
        else:
            logDetail = "%s日更新-追剧提醒推送数量为%s,设备量为%s,推送累计次数为%s,进入主页的数量为%s,比例为%s,播放剧集的数量为%s,比例为%s,自动息屏或用户主动关机的数量为%s,比例为%s" % (
                toMonthDay, newPushNum, newActiveIdNum, cumulatePushNum, homeNum, homeRate, okNum, okRate, exitNum, exitRate)
            print(logDetail)
            # 更新追剧提醒推送成功率
            updateSql = """
                            update analysis_zjtx_push_rate set show_num = %s,show_device_num = %s ,cumulate_num = %s,home_num = %s ,home_rate = %s,play_num = %s,play_rate = %s,shutdown_num = %s ,shutdown_rate = %s ,update_time = now()  where
                                ref_date = '%s' 
                        """ % (newPushNum, newActiveIdNum,cumulatePushNum, homeNum, homeRate, okNum, okRate, exitNum, exitRate, toMonthDay);
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 49
        jobName = '核心指标-追剧提醒-服务推送成功率'
        tableName = 'analysis_zjtx_push_rate'
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value(%s,'%s','%s','%s','%s')""" % (jobId,jobName,detail,tableName,ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    #today = todayYMD()
    date = getdate(1)
    monthdate = getMonthdate(1)
    #today = "2021-12-13"
    print ("%s 日期,更新<数据分析-追剧提醒-服务推送成功率>报表" % date)
    insertPushRate(date)
    insertPushRateMonth(date, monthdate)

